# encoding=utf-8
import logging
import os.path
import sqlite3
import traceback

from AppConfig import AppConfig, SQL_NAME


def create_conn():
    sql_path = os.path.join(AppConfig.CACHE_PATH, SQL_NAME)
    if os.path.exists(sql_path):
        return sqlite3.connect(sql_path)
    else:
        conn = sqlite3.connect(sql_path)
        conn.execute('''CREATE TABLE {}
                        (id      INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
                         name    TEXT     NOT NULL,
                         deal    INTEGER  NOT NULL);'''.format(AppConfig.SQL_TABLE_NAME))
        conn.commit()
        return conn


def insert_name_db(conn: sqlite3.Connection, filename):
    # 查询是否存在
    exist_query = conn.execute("SELECT id, name from {} where name='{}'".format(AppConfig.SQL_TABLE_NAME, filename))
    for row in exist_query:
        if row[1] == filename:
            return row[0]

    # 插入
    insert_query = conn.execute("INSERT INTO {} (name, deal) VALUES ('{}', '{}')".format(AppConfig.SQL_TABLE_NAME, filename, 1))
    conn.commit()
    return insert_query.lastrowid


def query_name_db(conn: sqlite3.Connection):
    all_query = conn.execute("SELECT name from {}".format(AppConfig.SQL_TABLE_NAME))
    name_list = []
    for row in all_query:
        name_list.append(row[0])

    return name_list


def delete_name_db(conn: sqlite3.Connection, name):
    try:
        conn.execute("DELETE from {} where name='{}'".format(AppConfig.SQL_TABLE_NAME, name))
        conn.commit()
    except Exception as e:
        logging.exception(traceback.format_exc())